#use encoding 'utf-8';
binmode STDOUT, ":utf8";
use DBI;
use CGI;
#use strict;
$dbname=shift;
$maindir = shift;
$dbistring ="dbi:ODBC:$dbname";
my $dbh= DBI->connect ($dbistring,'','') || die "could not connect\n";


my $query1="select distinct districts.district_name,UP40Musterrollmaster0910.MSR_no,
exe_agency.exe_agency_code,exe_agency.agency_name,UP40Musterrollmaster0910.work_code, UPWork_detail.work_name

from (((UP40Musterrollmaster0910 inner join districts on UP40Musterrollmaster0910.district_code=districts.district_code)
inner join UPWork_detail on  UPWork_detail.work_code=UP40Musterrollmaster0910.work_code)
inner join exe_agency on exe_agency.exe_agency_code=UPWork_detail.exe_agency_code)";
my $sth1=$dbh->prepare($query1);
$sth1->execute();
$count=1;
my $query="SELECT    UPWork_detail.work_code,UPWork_detail.work_name,UPWork_detail.exe_agency_code,
					UP40MustRoll0910.reg_no,UP40MustRoll0910.applicant_name, 
					UP40MustRoll0910.A1,UP40MustRoll0910.A2,UP40MustRoll0910.A3,
		UP40MustRoll0910.A4,UP40MustRoll0910.A5,UP40MustRoll0910.A6,
		UP40MustRoll0910.A7,UP40MustRoll0910.A8,UP40MustRoll0910.A9,
		UP40MustRoll0910.A10,UP40MustRoll0910.A11,UP40MustRoll0910.A12,
		UP40MustRoll0910.A13,UP40MustRoll0910.A14,UP40MustRoll0910.A15,
		UP40MustRoll0910.A16,UP40MustRoll0910.Tot_Attend,  UP40MustRoll0910.amt_paid_measure,Up40Registration.head_of_household,Up40Registration.father_or_husband_name
		FROM (UP40MustRoll0910 INNER JOIN UPWork_detail on 
		(UP40MustRoll0910.Work_Code = UPWork_detail.work_code) )
		inner join Up40Registration on Up40Registration.reg_no=UP40MustRoll0910.reg_no
		WHERE  UP40MustRoll0910.MSR_No=?
		";
		$sth=$dbh->prepare($query);
while (@arry=$sth1->fetchrow_array ()) {
		$district=$arry[0];
		$musterrollno=$arry[1];
		
		$musterrolldir="$maindir/$district/muster_rolls";
		print $musterrollno," ";
		if (! -d $musterrolldir) { mkdir $musterrolldir||die " error in executing\n";;}
			
		open $fh,"> $musterrolldir/$musterrollno.html"||die " error in executing\n";;
		binmode $fh ,":utf8";
		$sth->execute($musterrollno)||die " error in executing\n";
					
				
			print $fh qq(<html xmlns="http://www.w3.org/1999/xhtml">
					<head>
					<title>Work details</title>
					<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
					<meta name="robots" content="index, follow" />

					<link rel="stylesheet" href="../../styles.css" type="text/css" />
					 </head>);
		#print $fh qq(<head> <link rel=stylesheet href="../../../style.css"></head>);
			print $fh qq(<body style="margin: 0px; padding: 0px;");
			print $fh qq(<h3 align=center> Muster Roll No:$musterrollno <br>Work Id:$arry[4] Work Name:$arry[5] Agency Name:$arry[3] </h3>);

			print $fh qq(<table  style="height: 100%;width:70%;margin-top:40;" align=center cellpadding="10" cellspacing="0" border="1" >\n); # manipulte to look like you want it
	 
			print $fh "<thead>";
			print $fh "<th>Sl.No.</th><th>Name of Worker</th>";
			print $fh "<th>A1</th><th>A2</th><th>A3</th><th>A4</th><th>A5</th><th>A6</th><th>A7</th><th>A8</th><th>A9</th><th>A10</th><th>A11</th><th>A12</th><th>A13</th><th>A14</th><th>A15</th><th>A16</th>";
			print $fh "<th>Total Attend</th><th>Total Paid</th>";
			print $fh "</thead>";
			$tot_paid=0;
			
			while (@a=$sth->fetchrow_array ()) {

			print $fh "<tr>\n";
			print $fh "<td>$count</td>\n";
			print $fh "<td> $a[4]<br><font size=-2>$a[23]-$a[24]</font><br><font size=-2>$a[3]</font></br></td>";
			print $fh "<td> $a[5]</td>\n";
			print $fh "<td> $a[6]</td>\n";
			print $fh "<td> $a[7]</td>\n";
			print $fh "<td> $a[8]</td>\n";
			print $fh "<td> $a[9]</td>\n";
			print $fh "<td> $a[10]</td>\n";
			print $fh "<td> $a[11]</td>\n";
			print $fh "<td> $a[12]</td>\n";
			print $fh "<td> $a[13]</td>\n";
			print $fh "<td> $a[14]</td>\n";
			print $fh "<td> $a[15]</td>\n";
			print $fh "<td> $a[16]</td>\n";
			print $fh "<td> $a[17]</td>\n";
			print $fh "<td> $a[18]</td>\n";
			print $fh "<td> $a[19]</td>\n";
			print $fh "<td> $a[20]</td>\n";
			print $fh "<td> $a[21]</td>\n";
			print $fh "<td> $a[22]</td>\n";
			print $fh "</tr>\n";
			$tot_paid+=$a[22];
			$count++;
					
			}
			#@mustrolls=();
		
		print $fh "</table>";
		print $fh "<h3> Total paid: $tot_paid </h3>";
		print $fh "</body></html>";
		close $fh;
		$count=1;
		#exit;
}